﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Omu.ValueInjecter;

namespace Uber.Data
{
    using Core.Repository;

    public class BaseRepo
    {
        protected readonly string Cs;

        public BaseRepo(IConnectionFactory connFactory)
        {
            this.Cs = connFactory.GetConnectionString();
        }
    }

    public class FieldsBy : KnownTargetValueInjection<string>
    {
        private IEnumerable<string> ignoredFields = new string[] { };
        private string format = "{0}";
        private string nullFormat;
        private string glue = ",";

        public FieldsBy SetGlue(string g)
        {
            glue = " " + g + " ";
            return this;
        }

        public FieldsBy IgnoreFields(params string[] fields)
        {
            ignoredFields = fields;
            return this;
        }

        public FieldsBy SetFormat(string f)
        {
            format = f;
            return this;
        }

        public FieldsBy SetNullFormat(string f)
        {
            nullFormat = f;
            return this;
        }

        protected override void Inject(object source, ref string target)
        {
            var sourceProps = source.GetProps();
            var s = string.Empty;
            for (var i = 0; i < sourceProps.Count; i++)
            {
                var prop = sourceProps[i];
                if (ignoredFields.Contains(prop.Name)) continue;
                if (prop.GetValue(source) == DBNull.Value && nullFormat != null)
                    s += string.Format(nullFormat, prop.Name);
                else
                    s += string.Format(format, prop.Name) + glue;
            }
            s = s.RemoveSuffix(glue);
            target += s;
        }
    }

    public class SetParamsValues : KnownTargetValueInjection<SqlCommand>
    {
        private IEnumerable<string> ignoredFields = new string[] { };
        private string prefix = string.Empty;

        public SetParamsValues Prefix(string p)
        {
            prefix = p;
            return this;
        }

        public SetParamsValues IgnoreFields(params string[] fields)
        {
            ignoredFields = fields.AsEnumerable();
            return this;
        }

        protected override void Inject(object source, ref SqlCommand cmd)
        {
            if (source == null) return;
            var sourceProps = source.GetProps();

            for (var i = 0; i < sourceProps.Count; i++)
            {
                var prop = sourceProps[i];

                if (ignoredFields.Contains(prop.Name)) continue;

                if (prop.PropertyType == typeof(string[]))
                {
                    var value = prop.GetValue(source);
                    if (value == null) continue;
                    var val = (string[])value;

                    for (var j = 0; j < val.Length; j++)
                    {
                        cmd.Parameters.AddWithValue("@" + prefix + prop.Name + j, val[j]);
                        Console.Out.WriteLine("add:@" + prefix + prop.Name + j + " value:" + val[j]);
                    }
                }
                else
                {
                    var value = prop.GetValue(source) ?? DBNull.Value;
                    cmd.Parameters.AddWithValue("@" + prefix + prop.Name, value);

                    Console.Out.WriteLine("add:@" + prefix + prop.Name + " value:" + value);
                }
            }
        }
    }

    public class ReaderInjection : KnownSourceValueInjection<IDataReader>
    {
        protected override void Inject(IDataReader source, object target)
        {
            for (var i = 0; i < source.FieldCount; i++)
            {
                var activeTarget = target.GetProps().GetByName(source.GetName(i), true);
                if (activeTarget == null) continue;

                var value = source.GetValue(i);
                if (value == DBNull.Value) continue;

                activeTarget.SetValue(target, value);
            }
        }
    }

    public static class DbUtil
    {
        public static IEnumerable<T> GetWhere<T>(object where, string cs) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from " + DbConvention.Table(typeof(T)) + " where "
                        .InjectFrom(new FieldsBy()
                        .SetFormat("{0}=@{0}")
                        .SetNullFormat("{0} is null")
                        .SetGlue("and"),
                        where);
                    Console.Out.WriteLine(cmd.CommandText);
                    cmd.InjectFrom<SetParamsValues>(where);
                    conn.Open();

                    using (var dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom<ReaderInjection>(dr);
                            yield return o;
                        }
                    }
                }
            }
        }

        public static int Delete<T>(int id, string cs)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "delete from " + DbConvention.Table(typeof(T)) + " where id=" + id;

                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }

        ///<returns> the id of the inserted object </returns>
        public static Guid Insert(object o, string cs)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "insert " + DbConvention.Table(o) + " ("
                    .InjectFrom(new FieldsBy().IgnoreFields(DbConvention.Pk(o)), o)
                    + ") output inserted." + DbConvention.Pk(o) + " values("
                    .InjectFrom(new FieldsBy().IgnoreFields(DbConvention.Pk(o)).SetFormat("@{0}"), o)
                    + ")";
                Console.WriteLine(cmd.CommandText);
                cmd.InjectFrom(new SetParamsValues().IgnoreFields(DbConvention.Pk(o)), o);

                conn.Open();
                return (Guid)cmd.ExecuteScalar();
            }
        }

        public static int Update(object o, string cs)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update " + DbConvention.Table(o) + " set "
                    .InjectFrom(new FieldsBy().IgnoreFields(DbConvention.Pk(o)).SetFormat("{0}=@{0}"), o)
                    + " where " + DbConvention.Pk(o) + " = @" + DbConvention.Pk(o);

                cmd.InjectFrom<SetParamsValues>(o);

                conn.Open();
                return Convert.ToInt32(cmd.ExecuteScalar());
            }
        }

        public static int UpdateWhatWhere<T>(object what, object where, string cs)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "update " + DbConvention.Table(typeof(T)) + " set "
                    .InjectFrom(new FieldsBy().SetFormat("{0}=@{0}"), what)
                    + " where "
                    .InjectFrom(new FieldsBy()
                    .SetFormat("{0}=@wp{0}")
                    .SetNullFormat("{0} is null")
                    .SetGlue("and"),
                    where);

                cmd.InjectFrom<SetParamsValues>(what);
                cmd.InjectFrom(new SetParamsValues().Prefix("wp"), where);

                conn.Open();
                return Convert.ToInt32(cmd.ExecuteScalar());
            }
        }

        public static int InsertNoIdentity(object o, string cs)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "insert " + DbConvention.Table(o) + " ("
                    .InjectFrom(new FieldsBy().IgnoreFields("Id"), o) + ") values("
                    .InjectFrom(new FieldsBy().IgnoreFields("Id").SetFormat("@{0}"), o) + ")";

                cmd.InjectFrom<SetParamsValues>(o);

                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }

        /// <returns>rows affected</returns>
        public static int ExecuteNonQuerySp(string sp, string cs, object parameters)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = sp;
                    cmd.InjectFrom<SetParamsValues>(parameters);
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static int ExecuteNonQuery(string query, string cs, object parameters)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = query;
                    cmd.InjectFrom<SetParamsValues>(parameters);
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static IEnumerable<T> ExecuteReaderSp<T>(string sp, string cs, object parameters) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = sp;
                    cmd.InjectFrom<SetParamsValues>(parameters);
                    conn.Open();
                    using (var dr = cmd.ExecuteReader())
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom<ReaderInjection>(dr);
                            yield return o;
                        }
                }
            }
        }

        public static IEnumerable<T> ExecuteReaderSpValueType<T>(string sp, string cs, object parameters)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = sp;
                    cmd.InjectFrom<SetParamsValues>(parameters);
                    conn.Open();
                    using (var dr = cmd.ExecuteReader())
                        while (dr.Read())
                        {
                            yield return (T)dr.GetValue(0);
                        }
                }
            }
        }

        public static int Count<T>(string cs)
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select count(*) from " + DbConvention.Table(typeof(T));
                    conn.Open();

                    return (int)cmd.ExecuteScalar();
                }
            }
        }

        public static IEnumerable<T> GetAll<T>(string cs) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from " + DbConvention.Table(typeof(T));
                    conn.Open();

                    using (var dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom<ReaderInjection>(dr);
                            yield return o;
                        }
                    }
                }
            }
        }

        public static T Get<T>(Guid id, string cs) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select * from " + DbConvention.Table(typeof(T)) +
                    " where " + DbConvention.Pk(typeof(T)) + " = @id";
                cmd.Parameters.AddWithValue("id", id);
                conn.Open();
                Console.WriteLine(cmd.CommandText);
                using (var dr = cmd.ExecuteReader())
                    while (dr.Read())
                    {
                        var o = new T();
                        o.InjectFrom<ReaderInjection>(dr);
                        return o;
                    }
            }
            return default(T);
        }

        public static string ArrayLike(string[] a, string key, string param)
        {
            if (a == null) return " 1 = 1 ";
            var r = "";
            for (var i = 0; i < a.Length; i++)
            {
                r += key + " like @" + param + i;
                if (i < a.Length - 1) r += " or ";
            }
            return r;
        }

        public static IEnumerable<T> QueryToList<T>(string query, string cs, object parameters) where T : new()
        {
            using (var conn = new SqlConnection(cs))
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = query;
                    cmd.InjectFrom<SetParamsValues>(parameters);
                    conn.Open();
                    using (var dr = cmd.ExecuteReader())
                        while (dr.Read())
                        {
                            var o = new T();
                            o.InjectFrom<ReaderInjection>(dr);
                            yield return o;
                        }
                }
            }
        }

        public static int DeleteWhere<T>(object where, string cs)
        {
            using (var conn = new SqlConnection(cs))
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "delete from " + DbConvention.Table(typeof(T))
                                  + " where "
                                        .InjectFrom(new FieldsBy()
                                                        .SetFormat("{0}=@wp{0}")
                                                        .SetNullFormat("{0} is null")
                                                        .SetGlue("and"),
                                                    where);

                cmd.InjectFrom(new SetParamsValues().Prefix("wp"), where);

                conn.Open();
                return Convert.ToInt32(cmd.ExecuteScalar());
            }
        }
    }

    public static class DbConvention
    {
        private static string Schema(Type type)
        {
            return type.Name+"s";
        }

        public static string Table(Type type)
        {
            return type.Name + "s";
        }

        public static string Table(object o)
        {
            return Table(o.GetType());
        }

        public static string Pk(object o)
        {
            return Pk(o.GetType());
        }

        public static string Pk(Type type)
        {
            return "Id";
        }
    }

    public class Repo
    {
        protected string Cs;

        public Repo()
        {
            Cs = ConfigurationManager.ConnectionStrings["Db"].ConnectionString;
        }
    }

    public interface IURepo
    {
        int UpdateWhatWhere<T>(object what, object where);
        IEnumerable<T> GetWhere<T>(object where) where T : new();
        Guid Insert<T>(T o);
        int Insertn<T>(T o);
        T Get<T>(Guid id) where T : new();
        int Update<T>(T o);
        void MarkDeleted<T>(Guid id);
        void DeleteWhere<T>(object o);
    }

    public class URepo : Repo, IURepo
    {
        public virtual int UpdateWhatWhere<T>(object what, object where)
        {
            return DbUtil.UpdateWhatWhere<T>(what, where, Cs);
        }

        public IEnumerable<T> GetWhere<T>(object where) where T : new()
        {
            return DbUtil.GetWhere<T>(where, Cs);
        }

        public Guid Insert<T>(T o)
        {
            return DbUtil.Insert(o, Cs);
        }

        public int Insertn<T>(T o)
        {
            return DbUtil.InsertNoIdentity(o, Cs);
        }

        public T Get<T>(Guid id) where T : new()
        {
            return DbUtil.Get<T>(id, Cs);
        }

        public int Update<T>(T o)
        {
            return DbUtil.Update(o, Cs);
        }

        public void MarkDeleted<T>(Guid id)
        {
            DbUtil.UpdateWhatWhere<T>(new { IsDeleted = true }, new { id }, Cs);
        }

        public void DeleteWhere<T>(object o)
        {
            DbUtil.DeleteWhere<T>(o, Cs);
        }
    }
}